# -*- coding: utf-8 -*-
import os
import socket
import SocketServer 
import pyodbc
import logging
import globalvar
import traceback
import datetime
import ConfigParser
import urllib2
import re
import ems_database
import string,time,sys,os,smtplib
import pprint



class UCHandler(SocketServer.BaseRequestHandler):
    def parseprotocol(self):
        globalvar.socketlogging.debug('data  =%s'%(self.protocal_data))
        self.split_list = self.protocal_data.split('`')
        self.commandid=self.split_list[0]
        self.commandtype=self.split_list[1]
        self.employeecode=self.split_list[2]        
        self.datetimestr=self.split_list[3]
        self.content=self.split_list[4]

        self.all_values_list=self.content.split('\t')        
        for i in range(0,len(self.all_values_list)):
            self.all_values_list[i] = self.all_values_list[i].encode('utf-8')
            globalvar.socketlogging.debug("self.all_values_list[%d] = %s"%(i,self.all_values_list[i]))
                        
        globalvar.socketlogging.debug('commandid =%s'%(self.commandid))
        globalvar.socketlogging.debug('commandtype =%s'%(self.commandtype))
        globalvar.socketlogging.debug("employeecode =%s"%(self.employeecode))                
        globalvar.socketlogging.debug("datetimetstr is %s"%(self.datetimestr))
        globalvar.socketlogging.debug("content is %s"%(self.content))
        
    
    def SendStatus(self,status):
        content_str=self.commandid + globalvar.SPLITCHAR + self.commandtype + globalvar.SPLITCHAR +  self.GetCurrentTime() + globalvar.SPLITCHAR  + status + globalvar.SPLITCHAR 
        self.SendContent(content_str)
        
    def GetSendStrPrefix(self):
        new_str  = self.commandid + globalvar.SPLITCHAR + self.commandtype +  globalvar.SPLITCHAR + self.GetCurrentTime() + globalvar.SPLITCHAR
        return new_str

    def SendContent(self,content):
        content = content + globalvar.ENDCHAR.encode('utf-8')        
        str_utf8 = content.decode('utf-8')
        globalvar.socketlogging.debug("send str :%s"%(str_utf8))
        str_utf16 = str_utf8.encode('utf-16-le')
        length_str = '%08x'%(len(str_utf16))
        sendstr = length_str.encode('utf-16-le') + globalvar.SPLITCHAR.encode('utf-16-le') + str_utf16
        self.request.sendall(sendstr)

    def finish(self):
        globalvar.socketlogging.debug("Disconnect :")
        globalvar.socketlogging.debug(self.client_address)

    def setup(self):
        globalvar.socketlogging.info("There is a connection:")
        globalvar.socketlogging.info(self.client_address)
        

    def handle(self):

        self.db = ems_database.emsdatabase()
        while 1:
            try:
                header_str = self.request.recv(16)
                if(len(header_str)<16):
                    globalvar.socketlogging.error("receive header len str <16,recv len = %d ,and the content is %s"%(len(header_str),header_str))
                    break
                
                header_str=header_str.decode('utf-16-le')            
                globalvar.socketlogging.debug("header str  is %s:"%(header_str))            
                self.splitstr=self.request.recv(2)
                if( len(self.splitstr)< 2):
                    globalvar.socketlogging.error("can not get the split char after the length")
                    break
                globalvar.socketlogging.debug("start to receive the content")
                self.bodylen = int(header_str,16)            
                chunk=self.request.recv(self.bodylen)
                while len(chunk) < self.bodylen:
                    chunk=chunk+self.request.recv(self.bodylen-len(chunk))
            
                self.protocal_data=chunk
            
                #decode the unicode
                self.protocal_data = self.protocal_data.decode('utf-16-le')
                self.parseprotocol()
                if(self.commandid == globalvar.COMMAND_LOGIN):
                    self.HandleLogin()
                elif (self.commandid == globalvar.COMMAND_DOWNLOAD_RECEIVE_BILL ):
                    self.HandleDownloadReceiveBill()
                elif(self.commandid == globalvar.COMMAND_QUERY_XML):
                    self.HandleQueryXML()               
                elif(self.commandid == globalvar.COMMAND_NEW_RECEVIE_BILL):
                    self.HandleNewRecevieBill()                    
                elif(self.commandid == globalvar.COMMAND_UPLOAD_RECEVIE_BILL):
                    self.HandleUploadRecevieBill()
                elif(self.commandid == globalvar.COMMAND_NEW_PROBLEM_BILL):
                    self.HandleNewProblemBill()
                elif(self.commandid == globalvar.COMMAND_UPDATE_SIGNIN_BILL):
                    self.HandleUpdateSigninBill()
                globalvar.socketlogging.debug("finish a loop")    
            
            except socket.timeout:
                globalvar.socketlogging.error("time out error")
                break
            except socket.error:
                globalvar.socketlogging.error("socke error")
                #self.SendStatus(globalvar.STATUS_ERROR_SOCKET)
                break
            except:
                exption_str=traceback.format_exc()
                globalvar.socketlogging.error( "Unexpected error:\n %s ",exption_str )
                self.SendStatus(globalvar.STATUS_ERROR_UNKOWN)
                break
            
    def HandleNewRecevieBill(self):
        self.db.newreceivebill(self.all_values_list)
        self.SendStatus(globalvar.STATUS_OK)
        
    def HandleUploadRecevieBill(self):
        self.db.uploadreceivebill(self.all_values_list)
        self.SendStatus(globalvar.STATUS_OK)
        
    def HandleUpdateSigninBill(self):
        bill_no = self.all_values_list[0]
        signin_name = self.all_values_list[1]
        globalvar.socketlogging.debug("bill_no %s"%(bill_no))
        globalvar.socketlogging.debug("signin_name %s"%(signin_name))
        bill_no = bill_no.decode('utf-8').encode('gbk')
        signin_name = signin_name.decode('utf-8').encode('gbk')
        insert_record_str = "insert into  TAB_GPRS_SIGN_QY (%s) values(\
        '%s','%s','signinsite','signname','001',sysdate,\
        'no remark');"%(globalvar.TAB_GPRS_SIGN_QY_COLUMN,bill_no,signin_name)
        globalvar.databaselogging.debug("insert_record_str = %s"%(insert_record_str))
        globalvar.cursor.execute(insert_record_str)
        globalvar.conn.commit()
        self.SendStatus(globalvar.STATUS_OK)

    def HandleDownloadReceiveBill(self):
        
        globalvar.socketlogging.debug("Handle Download Receive Bill")
        #first check the state 0 
        query_str = "select MSG_CONTENT,GUID from TAB_GPRS_MSG where EMPLOYEE_CODE = '%s' and MSG_STATE = 0;"%(self.employeecode.encode('gbk'))
        globalvar.cursor.execute(query_str)
        globalvar.databaselogging.debug("query_str = %s"%(query_str))
        records = globalvar.cursor.fetchall()
        num_task = len(records)
        num_task_str = '%08x'%(num_task)
        globalvar.databaselogging.debug("there are %d download recive task for your device"%(num_task))

        content_str = self.GetSendStrPrefix()                
        content_str = content_str.encode('utf-8') + num_task_str.encode('utf-8')                

        for i in range(0,num_task):

            record_str = records[i][0]
            record_str_list=record_str.split()
            serial_number=record_str_list[0]
            
            guid_str = records[i][1]
            
            globalvar.databaselogging.debug("the record_str is %s"%(record_str))
            globalvar.databaselogging.debug("the guid_str is %s"%(guid_str))
                        
            content_str = content_str + globalvar.SPLITCHAR + record_str.decode('gbk').encode('utf-8')

            globalvar.socketlogging.debug("serial_number=%s"%(serial_number))
            update_str = "UPDATE TAB_GPRS_MSG SET MSG_STATE=1,SERIAL_NUMBER='%s'  WHERE EMPLOYEE_CODE='%s' AND MSG_STATE=0 AND GUID = '%s';"%(serial_number,self.employeecode.encode('gbk'),guid_str)
            globalvar.databaselogging.debug("upate_str = %s"%(update_str))
            globalvar.cursor.execute(update_str)
            globalvar.conn.commit()

        content_str = content_str + globalvar.SPLITCHAR     

        self.SendContent(content_str)
        globalvar.conn.commit()


    def HandleQueryXML(self):
        billno=self.content
        keyparam='cwhKfBMrpCA6JKhtwDcZQ=='
        req = urllib2.Request('http://61.152.167.97/TaoBaoInterface/billQuery.do?billCode=%s&key=%s'%(billno,keyparam))
        response = urllib2.urlopen(req)
        the_page = response.read()
        content_str = self.GetSendStrPrefix()
        
        if(len(the_page) < 150):
            globalvar.databaselogging.debug( "Can not find the Bill Info")
            content_str = content_str + globalvar.STATUS_ERROR_NO_BILLNO.encode('utf-8')
        else:
            globalvar.databaselogging.debug( "Have the Bill Info")
            accept_time_pattern = re.compile(r"<AcceptTime>(.*?)<\/AcceptTime>")
            result = accept_time_pattern.findall(the_page)
            accept_time_str = result[-1].decode('gbk').encode('utf-8')
            
            accept_address_pattern = re.compile(r"<AcceptAddress>(.*?)<\/AcceptAddress>")
            result = accept_address_pattern.findall(the_page)
            accept_address_str = result[-1].decode('gbk').encode('utf-8')
            
            remark_pattern = re.compile(r"<Remark>(.*?)<\/Remark>")
            result = remark_pattern.findall(the_page)
            remark_str = result[-1].decode('gbk').encode('utf-8')

            
            content_str = content_str.encode('utf-8') + accept_time_str + globalvar.CONTENT_SPLIT.encode('utf-8') + accept_address_str + globalvar.CONTENT_SPLIT.encode('utf-8') + remark_str    
        self.SendContent(content_str)
        
    def HandleNewProblemBill(self):
        bill_no = self.all_values_list[0]
        problem_type = self.all_values_list[1]
        globalvar.socketlogging.debug("bill_no %s"%(bill_no))
        globalvar.socketlogging.debug("problem_type %s"%(problem_type))
        bill_no = bill_no.decode('utf-8').encode('gbk')
        problem_type = problem_type.decode('utf-8').encode('gbk')
        insert_record_str = "insert into  TAB_PROBLEM (%s) values(\
        '%s','%s','sendsite','register_site',sysdate,'retister name','problem_cause',\
        'revision','revision_man',sysdate,'provide_site',\
        sysdate,'department');"%(globalvar.TAB_PROBLEM_COLUMN,problem_type,bill_no)
        globalvar.socketlogging.debug("insert_record_str = %s"%(insert_record_str))
        globalvar.cursor.execute(insert_record_str)
        globalvar.conn.commit()
        self.SendStatus(globalvar.STATUS_OK)
        
        
        
    def HandleLogin(self):
        
        #delete the \n at the end of the content        
        all_login_info_list = self.content.split('\t')
        self.login_name =all_login_info_list[0]
        self.login_password =all_login_info_list[1]
        
        globalvar.databaselogging.debug("the username is %s,the password is %s"%(all_login_info_list[0],all_login_info_list[1]))
        globalvar.cursor.execute("select BAR_PASSWORD from TAB_EMPLOYEE where EMPLOYEE_CODE = ?",self.login_name.encode('gbk'))
        records = globalvar.cursor.fetchall()
        if len(records) > 0:            
            database_str = records[0][0]
            provide_str = self.login_password.encode('gbk')
            database_str = database_str.decode('gbk')
            pprint.pprint(self.login_name)
            pprint.pprint(database_str)            
            pprint.pprint(provide_str)
            globalvar.databaselogging.debug("database_str %s,provide_str %s"%(database_str,provide_str))
            if (database_str == provide_str):
                #login ok
                globalvar.databaselogging.debug('Login OK..... username = %s,password = %s'%(self.login_name,self.login_password))
                self.SendStatus(globalvar.STATUS_OK)                
            else:
                globalvar.databaselogging.debug('Login Fail !!! Password dismatch  username = %s,password = %s,database password = %s'%(self.login_name,self.login_password,database_str))    
                self.SendStatus(globalvar.STATUS_ERROR_LOGIN_PASSWORD_DISMATCH)
        else:
                #login error
                globalvar.databaselogging.debug('Login Error .no such username .. username = %s,password = %s'%(self.login_name,self.login_password))
                self.SendStatus(globalvar.STATUS_ERROR_LOGIN_NO_USER)
                                                  
    def GetCurrentTime(self):
        n=datetime.datetime.now()
        return n.strftime('%Y%m%d%H%M%S')
            
def main(*argv):

    config = ConfigParser.ConfigParser()
    config.read('server.cfg')
    logfile = config.get("Basic","Log")
    socket_port = config.get("Basic","Port")
    socket_port = int(socket_port)
    dns_str = config.get("Basic","DSN")    
    print "the dns_str is ",dns_str
    logfile = os.path.abspath(logfile)    

    logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(name)-12s  %(levelname)-8s %(message)s',
                    filename=logfile,
                    datefmt="%m-%d %H:%M:%S",        
                    filemode='a')

    console=logging.StreamHandler()
    console.setLevel(logging.DEBUG)
    formatter = logging.Formatter('%(name)-12s: %(levelname)-8s %(message)s')
    console.setFormatter(formatter)
    logging.getLogger('').addHandler(console)

    globalvar.socketlogging = logging.getLogger('Socket')
    globalvar.databaselogging = logging.getLogger('DataBase')

    if 1:
        try:
            globalvar.conn = pyodbc.connect(dns_str)
            globalvar.cursor = globalvar.conn.cursor()
        except :
            globalvar.databaselogging.error("Database setup error")
            return -1
    
    logging.debug("start server")

    
    myaddr = ('', socket_port)    
    print "the debug version,the socket_port = %d"%(socket_port)
    myserver = SocketServer.ThreadingTCPServer(myaddr,UCHandler)
    myserver.serve_forever()

if __name__ == '__main__':
    main()
